<?php


/*
Plugin Name: Synergy Search 
Plugin URI: http://synergy.com/
Description: Search wordpress plugin for Synergy Resource management system.
Version: 1.0
Author: Kevin Liu, Weranga Kaluarachchi
Author URI: http://synergy.com
License: GPL
*/

wp_enqueue_script('syn_search_script', '/wp-content/plugins/syn_search/syn_search.js');



function widget_syn_search() {
	echo $before_widget; 
	echo $before_title . '<h4>Search</h4>' . $after_title;
	if(isset($_GET["q"]) && strlen($_GET["q"]) >0 ) {
		live_search();
	}
			
	global $is_iphone;	
?>
<div class="synForm">
<form name="frmSynSearch" method="post" action="">
<?php if ($is_iphone) echo '<div class="synleft">'; ?>
  
  <label>Title:</label><br/>
  <input type="text" id="txtLiveSearch" name="txtLiveSearch" style="width:95%" onkeyup="showResult(this.value)" />
  <!-- <div id="livesearch"></div> -->
  <div id="livesearch" style="position: relative; top: 0px; width='100px;'" ></div>
  <?php 
  global $current_user;
  echo "<input type='hidden' id='hdUserID' value='$current_user->ID'/>";
  ?>
  
  Quick search by asset&rsquo;s title
  
  <p>
    <label>Project:</label><br />
      <select multiple="multiple" name="ldbSynSearchProject[]" id="ldbSynSearchProject" size="5" style="width:98%">   
<?php       	
        
	$arrProjID[] = Array();
    global $wpdb;
    $j=0;
      	global $current_user;
		$projContTable = $wpdb->prefix."projectcontributors";
		$projTable = $wpdb->prefix."projects";
		$myProjRows = $wpdb->get_results("SELECT $projTable.PROJ_ID, $projTable.PROJ_NAME
		FROM $projTable, $projContTable WHERE $projContTable.PROJ_ID = $projTable.PROJ_ID
		AND $projTable.IS_DELETED = '0' AND $projContTable.CONTRIBUTOR_ID = $current_user->ID ORDER BY $projTable.PROJ_NAME ASC");	
	
     	foreach ($myProjRows as $myProjRow) {
			//echo "<option value=$myProjRow->PROJ_ID>$myProjRow->PROJ_NAME</option>";
			$arrProjID[$j] = $myProjRow->PROJ_ID;
			$j++;
     	}
		$valProj = array_unique($arrProjID);
     	//for($f=0; $f<sizeof($valProj); $f++) {
     	foreach ($valProj as $projectID) {
     		$projResults = get_project_detail_by_ID($projectID);
     		foreach($projResults as $pRow) {
     			echo '<option value="'.$pRow->PROJ_ID.'">'.$pRow->PROJ_NAME.'</option>';			
     		}     		
     	}  
?>
      </select>
      </label>
  </p>
  
  <p>
  
  <label>Contributor:</label><br />
  <select multiple="multiple" name="ldbSynSearchContributor[]" id="ldbSynSearchContributor" size="5" style="width:98%">
<?php       	
 	$users = $wpdb->prefix."users";
 	$meta = $wpdb->prefix."usermeta";
 	$projectContributors = $wpdb->prefix."projectcontributors";
 	$contributors[] = Array();
 	$p = 0;
 	//for($i=0; $i<sizeof($arrProjID); $i++) { 		
 	foreach ($arrProjID as $projectID) {
 		$query = "SELECT DISTINCT u.id, a.meta_value as first_name, b.meta_value AS last_name FROM $users as u, $meta as a, $meta as b, $projectContributors as c 
 				  WHERE a.user_id = u.id AND b.user_id = u.id AND a.meta_key = 'first_name' AND b.meta_key = 'last_name' AND u.id = c.contributor_id AND c.proj_id = $projectID 
			 	  ORDER BY first_name";
		$rows = $wpdb->get_results($query);	
		foreach ($rows as $r) {
			$contributors[$p] = $r->id;
			$p++;
			//array_push($contributors, $r->id);
		} 		
 	}
 	$val = array_unique($contributors);
 	//for($l=0; $l<sizeof($val); $l++) { 	
 	foreach ($val as $userID) {	
 		$query = "SELECT DISTINCT u.id, a.meta_value as first_name, b.meta_value AS last_name FROM $users as u, $meta as a, $meta as b, $projectContributors as c 
 				  WHERE a.user_id = u.id AND b.user_id = u.id AND a.meta_key = 'first_name' AND b.meta_key = 'last_name' AND u.id = $userID"; 
		$userRows = $wpdb->get_results($query);
		foreach($userRows as $rows) {
			echo '<option value="' . $rows->id . '">' . $rows->first_name . ' ' . $rows->last_name .  '</option>';
		}		
 	}     
    ?>   
    </select>
    </label>   
  </p>
  <p>
      <label >Category:</label><br />
        <select  multiple="multiple" name="ldbSynSearchKeyword[]" id="ldbSynSearchKeyword" size="5" style="width:98%">
<?php      
	$arrKeywords[] = Array();
	$h = 0;
	$keywords = $wpdb->prefix."keywords"; 	
 	$projectKeywords = $wpdb->prefix."projectkeywords";
 	//for($i=0; $i<sizeof($arrProjID); $i++) {  
 	foreach ($arrProjID as $projectID) {		
    	$query = "SELECT DISTINCT $keywords.KEY_ID FROM $keywords, $projectKeywords WHERE $projectKeywords.PROJ_ID = $projectID AND $keywords.KEY_ID = $projectKeywords.KEY_ID ORDER BY $keywords.KEY_ID";
		$rows = $wpdb->get_results($query);
	
		foreach ($rows as $r) {
			$arrKeywords[$h] = $r->KEY_ID;
			$h++;
			//array_push($arrKeywords, $r->KEY_ID);
	 	} 	 	
 	}
 	
 	$valKey = array_unique($arrKeywords);
 	
    //for($m=0; $m<=sizeof($valKey); $m++) {
    foreach ($valKey as $keywordID) {
    	$keyResults = get_keywordss_detail_by_ID($keywordID);
    	foreach($keyResults as $kRow) {
    		echo '<option value="'.$kRow->KEY_ID.'">'.$kRow->KEYWORD.'</option>';			
    	}     		
    }
 	
 	
?>
    </select>
    </label>
  </p>
  <?php if ($is_iphone) {
  		echo '</div> <!-- end class="synleft" -->';
  		echo '<div class="synright">' . "\n";
  	} ?>
  <p>
    <label>Media Type:</label><br />
    	<div style="float:left;">
      <input type="checkbox" name="chkSynSearchDocument" value="document" /> Document<br/>
      <input type="checkbox" name="chkSynSearchImage" value="image" /> Picture <br/>
    	</div><div>
      <input type="checkbox" name="chkSynSearchAudio" value="audio" /> Audio <br/>
      <input type="checkbox" name="chkSynSearchVideo" value="video" /> Video <br />
    	</div>
  </p>
  
  <p><label>Date Range:</label></p> 
  
  <input type="text" name="dtpSysSearchFrom" style="width: 90px;"/>&nbsp;(2010-12-25)
   	<br/>to<br/>
	<input type="text" name="dtpSysSearchTo" style="width: 90px;"/>&nbsp;(2010-12-27)
	
  
  <p>  
     <input name="btnSynSearchSubmit" type="submit" value="find" />
     <input type='hidden' id='hdSearchString' name='hdSearchString'/>
  </p>
	  <?php if ($is_iphone) echo '</div> <!-- end of class="synright"-->'; ?>
</form>
</div> <!-- end of synform class-->
<?php

	echo $after_widget; 
}


/**
 * Get keywprd by keyword ID
 * @return	resultset  result set of keywords
 */
function get_keywordss_detail_by_ID($keyID) {
	echo "SELECT DISTINCT KEY_ID, KEYWORD FROM $keyTable WHERE KEY_ID = $keyID";
	
	global $wpdb;
	$keyTable = $wpdb->prefix."keywords";
	$myrows = $wpdb->get_results("SELECT DISTINCT KEY_ID, KEYWORD FROM $keyTable WHERE KEY_ID = $keyID");	
	echo "SELECT DISTINCT KEY_ID, KEYWORD FROM $keyTable WHERE KEY_ID = $keyID";
	return $myrows;	
}


/**
 * Get project by project ID
 * @return	resultset  result set of projects
 */
function get_project_detail_by_ID($projID) {
	global $wpdb;
	$projTable = $wpdb->prefix."projects";
	$myrows = $wpdb->get_results("SELECT DISTINCT PROJ_ID, PROJ_NAME FROM $projTable WHERE PROJ_ID = $projID AND IS_DELETED = '0'");	
	echo "SELECT DISTINCT PROJ_ID, PROJ_NAME FROM $projTable WHERE PROJ_ID = $projID AND IS_DELETED = '0' ";
	return $myrows;	
}


function live_search() {	
	global $wpdb;
	$table = $wpdb->prefix."asset";
	$myrows = $wpdb->get_results("SELECT RES_TITLE FROM wp_asset WHERE IS_DELETED = '0' ORDER BY RES_TITLE ASC");
	
	//get the q parameter from URL
	$q=$_GET["q"];

	//lookup all links from the xml file if length of q>0
	if (strlen($q) > 0) {
		$hint="";
		foreach ($myrows as $myrow) {
			if (stristr($myrow['RES_TITLE'],$q)) {
			      if ($hint=="") {
				        $hint=$myrow['RES_TITLE'];
				  }
			      else {
				        $hint=$hint."<br />".$myrow['RES_TITLE'];			        
			      }
		    }
			
		}
	}

	// Set output to "no suggestion" if no hint were found
	// or to the correct values
	if ($hint == "") {
	  $response="no suggestion";
	}
	else {
	  $response=$hint;
	}	
	//output the response
	echo $response;	
	
}



register_sidebar_widget('syn_search', 'widget_syn_search');
add_action('activate_syn_search/syn_searchmain.php', 'widget_syn_search');
?>

